﻿--Delete database
    --DROP DATABASE IF EXISTS otus_school;
--Create database
    --CREATE DATABASE otus_school;
--Delete table

    DROP TABLE IF EXISTS SCHEDULE,
    COURSES,
    TEACHERS,
    STUDENTS;

--Create tables

CREATE TABLE COURSES (COURSE_ID INT PRIMARY KEY,
    COURSE_NAME VARCHAR (255) NOT NULL);


CREATE TABLE TEACHERS(TEACHER_ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(255) NOT NULL,
    LAST_NAME VARCHAR(255) NOT NULL,
    MIDDLE_NAME VARCHAR(255),
    EMAIL VARCHAR(255),
    COURSE INT NOT NULL DEFAULT 1,
    CONSTRAINT TEACHER_COURSE FOREIGN KEY (COURSE) REFERENCES COURSES(COURSE_ID));


CREATE TABLE STUDENTS(STUDENT_ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(255) NOT NULL,
    LAST_NAME VARCHAR(255) NOT NULL,
    MIDDLE_NAME VARCHAR(255),
    EMAIL VARCHAR(255) NOT NULL,
    COURSE INT NOT NULL DEFAULT 1,
    CONSTRAINT STUDENT_COURSE FOREIGN KEY (COURSE) REFERENCES COURSES(COURSE_ID));


CREATE TABLE SCHEDULE (LESSON_DATE DATE NOT NULL,
    COURSE INT NOT NULL DEFAULT 1,
    TEACHER INT NOT NULL DEFAULT 1,
    CONSTRAINT LESSON_COURSE FOREIGN KEY (COURSE) REFERENCES COURSES(COURSE_ID),
    CONSTRAINT LESSON_TEACHER FOREIGN KEY (TEACHER) REFERENCES TEACHERS(TEACHER_ID));
	